package com.dhy.dao;

import com.dhy.dbCore.util.DbUtil;
import com.dhy.dbCore.JdbcTemplateFacade;
import com.dhy.dto.CourseDto;
import com.dhy.dto.MajorDto;

import java.util.List;

public class MajorDao {

    private JdbcTemplateFacade jt = DbUtil.getJT();

    public List<MajorDto> queryMajorByCName(String cName) {
        String sql = "select t.name as collegeName, m.name as majorName from major m join (select * from college where name = '" + cName + "') t where m.collegeId = t.id;";
        return jt.list(sql, MajorDto.class);
    }

    public Boolean deleteMajor(String mid) {
        String sql="delete from major where id = '"+mid+"';";
        return jt.delete(sql);
    }

    public List<CourseDto> queryMajorByCNo(Integer cno) {
        String sql="SELECT c.name courseName,c.type,s.sno,s.name,sc.score,sc.makeUpScore,sc.rebuildScore\n" +
                "FROM stu s\n" +
                "INNER JOIN courseSelection sc\n" +
                "ON s.id=sc.sid\n" +
                "INNER JOIN course c\n" +
                "ON c.id=sc.cid\n" +
                "WHERE c.id='"+cno+"'";
        return jt.list(sql, CourseDto.class);
    }
}
